import com.sun.corba.se.spi.orbutil.threadpool.Work;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.*;

/**
 * 第一次测试获取字符串
 */
public class TestRead_01{

    public static void main(String[] args) throws IOException {
        String Path = "C:\\Users\\73786\\Desktop\\11.xlsx";
        String Path2 = "C:\\Users\\73786\\Desktop\\22.xlsx";
        String Path3 = "C:\\Users\\73786\\Desktop\\第三表格.xlsx";

        String Path4 = "C:\\Users\\73786\\Desktop\\第三表格";
        for (int i = 2; i <= 10; i++) {
            String endName = i + ".xlsx";
            exportExcel(Path4+endName,readTest(Path,i),queryString(Path2,readTest(Path,i)));


        }

//        System.out.println("第一个表" + readTest(Path,19));
//        if (queryString(Path2, readTest(Path,19)).equals("")){
//            System.out.println("第二个表未找到该配对");
//        }else{
//            System.out.println("第二个表" + queryString(Path2, readTest(Path,19)));
//        }
//
//        exportExcel(Path3,readTest(Path,19),queryString(Path2,readTest(Path,19)));

    }

    /**
     * 获取第一个文件的字符串
     * @param Path
     * @return
     * @throws IOException
     */
    public static String readTest(String Path,int num) throws IOException {
        String cellValue = "";
        String value = "";
        FileInputStream file = null;
        ExcelPoiUtils excelPoiUtils = new ExcelPoiUtils();
        {
            try {
                file = new FileInputStream(Path);

                Workbook workbook = new XSSFWorkbook(file);

                Sheet sheet = workbook.getSheetAt(0);

                Row row = sheet.getRow(num-1);
                Cell cell = row.getCell(19);
                if (cell == null){
                    System.out.println("该表格为 空");
                }else{
                    cellValue = excelPoiUtils.getValue(cell);

//                    if (cellValue.length()>=12){
//                        value = cellValue.substring(0, 12);
//                    }else{
//                        System.out.println("字符串长度不够");
//                    }
                }

            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                file.close();
            }
        }
        return cellValue;
    }

    /**
     * 根据第一个字符串，找到第二表对应的内容
     * @param Path2
     * @param value
     * @return
     * @throws IOException
     */
    public static String queryString(String Path2,String value) throws IOException {
        FileInputStream file = null;
        String cellValue = "";
        String cellValueNext = "";
        int thisEnd = 0;
        ExcelPoiUtils excelPoiUtils = new ExcelPoiUtils();

        Workbook workbook = new XSSFWorkbook(new FileInputStream(Path2));

        Sheet sheet = workbook.getSheetAt(0);

        int rows = sheet.getLastRowNum();
        for (int row = 1; row <= rows; row++) {
            Row thisrow = sheet.getRow(row);

            Cell thiscell = thisrow.getCell(0);
            String thisvalue = excelPoiUtils.getValue(thiscell);

            if (value.length() > 12){
                if (thisvalue.equals(value.substring(0,12))){
//                System.out.println((row+1) + "__过程__" +thisvalue);
                    cellValue = thisvalue;
                    Cell cellEnd = thisrow.getCell(1);
                    cellValueNext = excelPoiUtils.getValue(cellEnd);
                }
            }else{
                System.out.println("字符串长度不够");
            }

    }
        return cellValueNext;
    }

    /**
     *  根据kv 生成excel
     * @param Path3
     * @param firstKey
     * @param secondValue
     * @throws IOException
     */
    public static void exportExcel(String Path3,String firstKey,String secondValue) throws IOException {
        boolean b = true;
        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet();

        Row titleRow = sheet.createRow(0);
        Cell titleCell1 = titleRow.createCell(0);
        titleCell1.setCellValue("项目定义");
        Cell titleCell12 = titleRow.createCell(1);
        titleCell12.setCellValue("描述");
        Cell titleCell13 = titleRow.createCell(2);
        titleCell13.setCellValue("单位");


        Row thisRow = sheet.createRow(1);//第一行创建和赋值
        Cell thisCell1 = thisRow.createCell(0);
        Cell thisCell2 = thisRow.createCell(1);

        thisCell1.setCellValue(firstKey);
        thisCell2.setCellValue(secondValue);

        FileOutputStream file = new FileOutputStream(Path3);

        workbook.write(file);

        file.close();

        System.out.println("文件生成完毕");

    }

}
